package com.yuncheng.spcyApi.utils;

import com.yuncheng.spcyApi.vo.sqsx.XcjcArrangeVo;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import utils.DateConversionUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.Date;
import java.util.List;

public class ExcelServiceUtils {
    private static final Logger log = LoggerFactory.getLogger(ExcelServiceUtils.class);

    /**
     * 导出 审评查验中心各科室现场检查计划安排汇总情况表
     * @param hzpList
     * @param ypzcList
     * @param ypscList
     * @param ypltList
     * @param ylqxList
     * @param response
     */
    public static void createXcjcArrangeExcel(List<XcjcArrangeVo> hzpList,
                                   List<XcjcArrangeVo> ypzcList,
                                   List<XcjcArrangeVo> ypscList,
                                   List<XcjcArrangeVo> ypltList,
                                   List<XcjcArrangeVo> ylqxList, HttpServletResponse response){
        String title = "审评查验中心各科室现场检查安排汇总情况表";

        // 创建 HSSFWorkbook 对象 (excel 的文档对象)
        HSSFWorkbook wb = new HSSFWorkbook();

        // 建立新的 sheet 对象（excel 的表单）
        HSSFSheet sheet = wb.createSheet(title);

        // 内容样式
        HSSFCellStyle contextCellStyle  = wb.createCellStyle();
        contextCellStyle.setAlignment(HorizontalAlignment.CENTER);
        contextCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        /*contextCellStyle.setBorderBottom(BorderStyle.THIN); // 下边框
        contextCellStyle.setBorderLeft(BorderStyle.THIN); // 左边框
        contextCellStyle.setBorderTop(BorderStyle.THIN); // 上边框
        contextCellStyle.setBorderRight(BorderStyle.THIN); // 右边框*/
        Font cellFont = wb.createFont();
        cellFont.setFontHeightInPoints((short)12);
        cellFont.setFontName("宋体");
        contextCellStyle.setFont(cellFont);
        contextCellStyle.setWrapText(true);// 设置自动换行

        // 标题样式
        HSSFCellStyle titleStyle = wb.createCellStyle();
        /*titleStyle.setBorderBottom(BorderStyle.THIN); // 下边框
        titleStyle.setBorderLeft(BorderStyle.THIN); // 左边框
        titleStyle.setBorderTop(BorderStyle.THIN); // 上边框
        titleStyle.setBorderRight(BorderStyle.THIN); // 右边框*/

        titleStyle.setAlignment(HorizontalAlignment.CENTER); // 水平居中
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中

        // 字体2
        HSSFFont font2 = wb.createFont();
        font2.setFontName("黑体");
        font2.setBold(true);
        font2.setFontHeightInPoints((short) 15);

        titleStyle.setFont(font2); // 选择需要用到的字体格式

        // 标题样式2
        HSSFCellStyle titleStyle2 = wb.createCellStyle();
        /*titleStyle2.setBorderBottom(BorderStyle.THIN); // 下边框
        titleStyle2.setBorderLeft(BorderStyle.THIN); // 左边框
        titleStyle2.setBorderTop(BorderStyle.THIN); // 上边框
        titleStyle2.setBorderRight(BorderStyle.THIN); // 右边框*/
        titleStyle2.setAlignment(HorizontalAlignment.CENTER); // 水平居中
        titleStyle2.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中
        // 字体2
        HSSFFont font3 = wb.createFont();
        font3.setFontName("宋体");
        font3.setBold(true);
        font3.setFontHeightInPoints((short) 12);
        titleStyle2.setFont(font3); // 选择需要用到的字体格式

        sheet.setColumnWidth(0, 900 * 10);
        sheet.setColumnWidth(1, 900 * 3);
        sheet.setColumnWidth(2, 900 * 6);
        sheet.setColumnWidth(3, 900 * 15);
        sheet.setColumnWidth(4, 900 * 15);
        sheet.setColumnWidth(5, 900 * 9);
        sheet.setColumnWidth(6, 900 * 15);
        sheet.setColumnWidth(7, 900 * 15);
        sheet.setColumnWidth(8, 900 * 15);
        sheet.setColumnWidth(9, 900 * 7);

        // 第一个参数代表列 id (从 0 开始), 第 2 个参数代表宽度值
//        sheet.setColumnWidth(0, 3766);

        // 在 sheet 里创建第一行，参数为行索引 (excel 的行)，可以是 0～65535 之间的任何一个
        HSSFRow row1 = sheet.createRow(0);
//        row1.setRowStyle(titleStyle);
        // 创建单元格（excel 的单元格，参数为列索引，可以是 0～255 之间的任何一个
        HSSFCell cell = row1.createCell(0);
        // 设置单元格内容
        cell.setCellValue("自治区食品药品审评查验中心现场检查计划安排情况表");
        cell.setCellStyle(titleStyle);
        // 合并单元格 CellRangeAddress 构造参数依次表示起始行，截至行，起始列， 截至列
        sheet.addMergedRegion(new CellRangeAddress(0,0,0,9));

        // 第二行
        HSSFRow row2 = sheet.createRow(1);
//        row2.setRowStyle(titleStyle);
        HSSFCell cell2 = row2.createCell(0);
        cell2.setCellValue("时间："+ DateConversionUtils.DateToString(new Date()));
        cell2.setCellStyle(titleStyle);
        sheet.addMergedRegion(new CellRangeAddress(1,1,0,9));

        // 第三行
        HSSFRow row3 = sheet.createRow(2);
        HSSFCell cell00 = row3.createCell(0);
        cell00.setCellValue("科室");
        cell00.setCellStyle(titleStyle2);

        HSSFCell cell1 = row3.createCell(1);
        cell1.setCellValue("序号");
        cell1.setCellStyle(titleStyle2);

        HSSFCell cell22 = row3.createCell(2);
        cell22.setCellValue("检查类型");
        cell22.setCellStyle(titleStyle2);

        HSSFCell cell3 = row3.createCell(3);
        cell3.setCellValue("企业名称");
        cell3.setCellStyle(titleStyle2);

        HSSFCell cell4 = row3.createCell(4);
        cell4.setCellValue("地址");
        cell4.setCellStyle(titleStyle2);

        HSSFCell cell5 = row3.createCell(5);
        cell5.setCellValue("企业联系人及电话");
        cell5.setCellStyle(titleStyle2);

        HSSFCell cell6 = row3.createCell(6);
        cell6.setCellValue("检查类型/范围");
        cell6.setCellStyle(titleStyle2);

        HSSFCell cell7 = row3.createCell(7);
        cell7.setCellValue("检查时间");
        cell7.setCellStyle(titleStyle2);

        HSSFCell cell8 = row3.createCell(8);
        cell8.setCellValue("检查员");
        cell8.setCellStyle(titleStyle2);

        HSSFCell cell9 = row3.createCell(9);
        cell9.setCellValue("备注");
        cell9.setCellStyle(titleStyle2);


        int hs = 3; // 行数
        int hzpSize = hzpList.size(); // 化妆品大小
        if (hzpSize > 0){

            // 科室
            HSSFRow row4 = sheet.createRow(hs);
            // 设置单元格内容
            HSSFCell cell0 = row4.createCell(0);
            cell0.setCellValue(hzpList.get(0).getKs());
            cell0.setCellStyle(titleStyle2);
            // 合并单元格 CellRangeAddress 构造参数依次表示起始行，截至行，起始列， 截至列
            if (hzpSize>1) {
                sheet.addMergedRegion(new CellRangeAddress(hs, hs + hzpSize - 1, 0, 0));
            }

            for (int i = 0; i<hzpSize; i++){
                HSSFRow row5 = null;
                if (i == 0){
                    row5 = row4;
                }else {
                    hs = hs + 1;
                    row5 = sheet.createRow(hs);
                }

                // 第二列开始
                HSSFCell cell10 = row5.createCell(1);
                cell10.setCellValue(i + 1);
                cell10.setCellStyle(contextCellStyle);

                HSSFCell cell11 = row5.createCell(2);
                cell11.setCellValue(hzpList.get(i).getFjclx());
                cell11.setCellStyle(contextCellStyle);

                HSSFCell cell12 = row5.createCell(3);
                cell12.setCellValue(hzpList.get(i).getFqymc());
                cell12.setCellStyle(contextCellStyle);

                HSSFCell cell13 = row5.createCell(4);
                cell13.setCellValue(hzpList.get(i).getFqyzszcdz());
                cell13.setCellStyle(contextCellStyle);

                HSSFCell cell14 = row5.createCell(5);
                cell14.setCellValue(hzpList.get(i).getFqylxr()+" "+hzpList.get(i).getFqylxrdh());
                cell14.setCellStyle(contextCellStyle);

                HSSFCell cell15 = row5.createCell(6);
                cell15.setCellValue(hzpList.get(i).getFcyjcfw());
                cell15.setCellStyle(contextCellStyle);

                HSSFCell cell16 = row5.createCell(7);
                if (hzpList.get(i).getFcykssj() != null && hzpList.get(i).getFcyjssj() != null) {
                    cell16.setCellValue(DateConversionUtils.DateToString(hzpList.get(i).getFcykssj()) + "至" + DateConversionUtils.DateToString(hzpList.get(i).getFcyjssj()));
                }
                cell16.setCellStyle(contextCellStyle);

                HSSFCell cell17 = row5.createCell(8);
                cell17.setCellValue(hzpList.get(i).getJcry());
                cell17.setCellStyle(contextCellStyle);

                HSSFCell cell18 = row5.createCell(9);
                cell18.setCellValue(hzpList.get(i).getBz());
                cell18.setCellStyle(contextCellStyle);
            }
        }

        // 药品注册
        int ypzcSize = ypzcList.size();
        if (ypzcSize > 0){
            hs = hs + 1;

            // 科室
            HSSFRow row4 = sheet.createRow(hs);
            // 设置单元格内容
            HSSFCell cell0 = row4.createCell(0);
            cell0.setCellValue(ypzcList.get(0).getKs());
            cell0.setCellStyle(titleStyle2);
            // 合并单元格 CellRangeAddress 构造参数依次表示起始行，截至行，起始列， 截至列
            if (ypzcSize>1) {
                sheet.addMergedRegion(new CellRangeAddress(hs, hs + ypzcSize - 1, 0, 0));
            }

            for (int i = 0; i<ypzcSize; i++){
                HSSFRow row5 = null;
                if (i == 0){
                    row5 = row4;
                }else {
                    hs = hs + 1;
                    row5 = sheet.createRow(hs);
                }

                // 第二列开始
                HSSFCell cell10 = row5.createCell(1);
                cell10.setCellValue(i + 1);
                cell10.setCellStyle(contextCellStyle);

                HSSFCell cell11 = row5.createCell(2);
                cell11.setCellValue(ypzcList.get(i).getFjclx());
                cell11.setCellStyle(contextCellStyle);

                HSSFCell cell12 = row5.createCell(3);
                cell12.setCellValue(ypzcList.get(i).getFqymc());
                cell12.setCellStyle(contextCellStyle);

                HSSFCell cell13 = row5.createCell(4);
                cell13.setCellValue(ypzcList.get(i).getFqyzszcdz());
                cell13.setCellStyle(contextCellStyle);

                HSSFCell cell14 = row5.createCell(5);
                cell14.setCellValue(ypzcList.get(i).getFqylxr()+" "+ypzcList.get(i).getFqylxrdh());
                cell14.setCellStyle(contextCellStyle);

                HSSFCell cell15 = row5.createCell(6);
                cell15.setCellValue(ypzcList.get(i).getFcyjcfw());
                cell15.setCellStyle(contextCellStyle);

                HSSFCell cell16 = row5.createCell(7);
                if (ypzcList.get(i).getFcykssj() != null && ypzcList.get(i).getFcyjssj() != null) {
                    cell16.setCellValue(DateConversionUtils.DateToString(ypzcList.get(i).getFcykssj()) + "至" + DateConversionUtils.DateToString(ypzcList.get(i).getFcyjssj()));
                }
                cell16.setCellStyle(contextCellStyle);

                HSSFCell cell17 = row5.createCell(8);
                cell17.setCellValue(ypzcList.get(i).getJcry());
                cell17.setCellStyle(contextCellStyle);

                HSSFCell cell18 = row5.createCell(9);
                cell18.setCellValue(ypzcList.get(i).getBz());
                cell18.setCellStyle(contextCellStyle);
            }
        }

        // 药品生产
        int ypscSize = ypscList.size();
        if (ypscSize > 0){
            hs = hs + 1;

            // 科室
            HSSFRow row4 = sheet.createRow(hs);
            // 设置单元格内容
            HSSFCell cell0 = row4.createCell(0);
            cell0.setCellValue(ypscList.get(0).getKs());
            cell0.setCellStyle(titleStyle2);
            // 合并单元格 CellRangeAddress 构造参数依次表示起始行，截至行，起始列， 截至列
            if (ypscSize>1) {
                sheet.addMergedRegion(new CellRangeAddress(hs, hs + ypscSize - 1, 0, 0));
            }

            for (int i = 0; i<ypscSize; i++){
                HSSFRow row5 = null;
                if (i == 0){
                    row5 = row4;
                }else {
                    hs = hs + 1;
                    row5 = sheet.createRow(hs);
                }

                // 第二列开始
                HSSFCell cell10 = row5.createCell(1);
                cell10.setCellValue(i + 1);
                cell10.setCellStyle(contextCellStyle);

                HSSFCell cell11 = row5.createCell(2);
                cell11.setCellValue(ypscList.get(i).getFjclx());
                cell11.setCellStyle(contextCellStyle);

                HSSFCell cell12 = row5.createCell(3);
                cell12.setCellValue(ypscList.get(i).getFqymc());
                cell12.setCellStyle(contextCellStyle);

                HSSFCell cell13 = row5.createCell(4);
                cell13.setCellValue(ypscList.get(i).getFqyzszcdz());
                cell13.setCellStyle(contextCellStyle);

                HSSFCell cell14 = row5.createCell(5);
                cell14.setCellValue(ypscList.get(i).getFqylxr()+" "+ypscList.get(i).getFqylxrdh());
                cell14.setCellStyle(contextCellStyle);

                HSSFCell cell15 = row5.createCell(6);
                cell15.setCellValue(ypscList.get(i).getFcyjcfw());
                cell15.setCellStyle(contextCellStyle);

                HSSFCell cell16 = row5.createCell(7);
                if (ypscList.get(i).getFcykssj() != null && ypscList.get(i).getFcyjssj() != null) {
                    cell16.setCellValue(DateConversionUtils.DateToString(ypscList.get(i).getFcykssj()) + "至" + DateConversionUtils.DateToString(ypscList.get(i).getFcyjssj()));
                }
                cell16.setCellStyle(contextCellStyle);

                HSSFCell cell17 = row5.createCell(8);
                cell17.setCellValue(ypscList.get(i).getJcry());
                cell17.setCellStyle(contextCellStyle);

                HSSFCell cell18 = row5.createCell(9);
                cell18.setCellValue(ypscList.get(i).getBz());
                cell18.setCellStyle(contextCellStyle);
            }
        }

        // 药品流通
        int ypltSize = ypltList.size();
        if (ypltSize > 0){
            hs = hs + 1;

            // 科室
            HSSFRow row4 = sheet.createRow(hs);
            // 设置单元格内容
            HSSFCell cell0 = row4.createCell(0);
            cell0.setCellValue(ypltList.get(0).getKs());
            cell0.setCellStyle(titleStyle2);
            // 合并单元格 CellRangeAddress 构造参数依次表示起始行，截至行，起始列， 截至列
            if (ypltSize>1) {
                sheet.addMergedRegion(new CellRangeAddress(hs, hs + ypltSize - 1, 0, 0));
            }

            for (int i = 0; i<ypltSize; i++){
                HSSFRow row5 = null;
                if (i == 0){
                    row5 = row4;
                }else {
                    hs = hs + 1;
                    row5 = sheet.createRow(hs);
                }

                // 第二列开始
                HSSFCell cell10 = row5.createCell(1);
                cell10.setCellValue(i + 1);
                cell10.setCellStyle(contextCellStyle);

                HSSFCell cell11 = row5.createCell(2);
                cell11.setCellValue(ypltList.get(i).getFjclx());
                cell11.setCellStyle(contextCellStyle);

                HSSFCell cell12 = row5.createCell(3);
                cell12.setCellValue(ypltList.get(i).getFqymc());
                cell12.setCellStyle(contextCellStyle);

                HSSFCell cell13 = row5.createCell(4);
                cell13.setCellValue(ypltList.get(i).getFqyzszcdz());
                cell13.setCellStyle(contextCellStyle);

                HSSFCell cell14 = row5.createCell(5);
                cell14.setCellValue(ypltList.get(i).getFqylxr()+" "+ypltList.get(i).getFqylxrdh());
                cell14.setCellStyle(contextCellStyle);

                HSSFCell cell15 = row5.createCell(6);
                cell15.setCellValue(ypltList.get(i).getFcyjcfw());
                cell15.setCellStyle(contextCellStyle);

                HSSFCell cell16 = row5.createCell(7);
                if (ypltList.get(i).getFcykssj() != null && ypltList.get(i).getFcyjssj() != null) {
                    cell16.setCellValue(DateConversionUtils.DateToString(ypltList.get(i).getFcykssj()) + "至" + DateConversionUtils.DateToString(ypltList.get(i).getFcyjssj()));
                }
                cell16.setCellStyle(contextCellStyle);

                HSSFCell cell17 = row5.createCell(8);
                cell17.setCellValue(ypltList.get(i).getJcry());
                cell17.setCellStyle(contextCellStyle);

                HSSFCell cell18 = row5.createCell(9);
                cell18.setCellValue(ypltList.get(i).getBz());
                cell18.setCellStyle(contextCellStyle);
            }
        }

        // 医疗器械
        int ypqxSize = ylqxList.size();
        if (ypqxSize > 0){
            hs = hs + 1;

            // 科室
            HSSFRow row4 = sheet.createRow(hs);
            // 设置单元格内容
            HSSFCell cell0 = row4.createCell(0);
            cell0.setCellValue(ylqxList.get(0).getKs());
            cell0.setCellStyle(titleStyle2);
            // 合并单元格 CellRangeAddress 构造参数依次表示起始行，截至行，起始列， 截至列
            if (ypqxSize > 1) {
                sheet.addMergedRegion(new CellRangeAddress(hs, hs + ypqxSize - 1, 0, 0));
            }

            for (int i = 0; i<ypqxSize; i++){
                HSSFRow row5 = null;
                if (i == 0){
                    row5 = row4;
                }else {
                    hs = hs + 1;
                    row5 = sheet.createRow(hs);
                }

                // 第二列开始
                HSSFCell cell10 = row5.createCell(1);
                cell10.setCellValue(i + 1);
                cell10.setCellStyle(contextCellStyle);

                HSSFCell cell11 = row5.createCell(2);
                cell11.setCellValue(ylqxList.get(i).getFjclx());
                cell11.setCellStyle(contextCellStyle);

                HSSFCell cell12 = row5.createCell(3);
                cell12.setCellValue(ylqxList.get(i).getFqymc());
                cell12.setCellStyle(contextCellStyle);

                HSSFCell cell13 = row5.createCell(4);
                cell13.setCellValue(ylqxList.get(i).getFqyzszcdz());
                cell13.setCellStyle(contextCellStyle);

                HSSFCell cell14 = row5.createCell(5);
                cell14.setCellValue(ylqxList.get(i).getFqylxr()+" "+ylqxList.get(i).getFqylxrdh());
                cell14.setCellStyle(contextCellStyle);

                HSSFCell cell15 = row5.createCell(6);
                cell15.setCellValue(ylqxList.get(i).getFcyjcfw());
                cell15.setCellStyle(contextCellStyle);

                HSSFCell cell16 = row5.createCell(7);
                if (ylqxList.get(i).getFcykssj() != null && ylqxList.get(i).getFcyjssj() != null) {
                    cell16.setCellValue(DateConversionUtils.DateToString(ylqxList.get(i).getFcykssj()) + "至" + DateConversionUtils.DateToString(ylqxList.get(i).getFcyjssj()));
                }
                cell16.setCellStyle(contextCellStyle);

                HSSFCell cell17 = row5.createCell(8);
                cell17.setCellValue(ylqxList.get(i).getJcry());
                cell17.setCellStyle(contextCellStyle);

                HSSFCell cell18 = row5.createCell(9);
                cell18.setCellValue(ylqxList.get(i).getBz());
                cell18.setCellStyle(contextCellStyle);
            }
        }

        // 输出
        /*FileOutputStream outputStream = null;
        try {
            outputStream = new FileOutputStream(new File("D:/test/审评查验中心各科室现场检查计划安排汇总情况表.xls"));
            wb.write(outputStream);
            outputStream.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }catch (IOException e) {
            e.printStackTrace();
        }*/

        //下载
        //设置文件名
        String nowDate = DateConversionUtils.DateToStringYYYYMMDD(new Date());
        String fileName = title+"-"+ nowDate + ".xls";

        response.reset();
        response.setCharacterEncoding("UTF-8");
        response.addHeader("Access-Control-Allow-Origin", "*");
        response.addHeader("Access-Control-Allow-Methods", "GET, POST, PUT, DELETE");
        response.setContentType("application/ms-excel;charset=UTF-8");//以流的形式对文件进行下载
        try {
            response.setHeader("Content-Disposition", "attachment;filename=" .concat(String.valueOf(URLEncoder.encode(fileName, "UTF-8"))));//对文件名编码,防止文件名乱码
            wb.write(response.getOutputStream());
            response.getOutputStream().close();
        } catch (UnsupportedEncodingException e) {
            log.error("导出excel表格出错【UnsupportedEncodingException】： {}",e.getMessage());
        } catch (IOException e) {
            log.error("导出excel表格出错【IOException】： {}",e.getMessage());
        }

    }

}
